﻿using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using ZhangBY.Config;


namespace ZhangBY.Data
{
    public class DBHelper
    {
        
        #region 私有变量

        private static object lockHelper = new object();

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string m_connStr = null;

        /// <summary>
        /// DbProviderFactory实例
        /// </summary>
        private static DbProviderFactory m_factory = null;

        /// <summary>
        /// ZhangBY!NT数据接口
        /// </summary>
        private static ADbSupplier m_supplier = null;

        /// <summary>
        /// Parameters缓存哈希表
        /// </summary>
        private static Hashtable m_paramCache = Hashtable.Synchronized(new Hashtable());

        #endregion

        #region 属性


        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnStr
        {
            get
            {
                if (m_connStr == null)
                    m_connStr = BaseConfigs.GetDBConnectString;                
                return m_connStr;
            }
            set { m_connStr = value; }
        }

        /// <summary>
        /// ADbSupplier抽象类接口
        /// </summary>
        public static ADbSupplier Supplier
        {
            get
            {
                if (m_supplier == null)
                {
                    lock (lockHelper)
                    {
                        try
                        {
                            m_supplier = (ADbSupplier)Activator.CreateInstance(Type.GetType(string.Format("ZhangBY.Data.{0}Supplier, ZhangBY.Data.{0}", BaseConfigs.GetDbType), false, true));
                        }
                        catch
                        {
                            throw new Exception("请检查ZBY.config中DbType节点数据库类型是否正确，例如：SqlServer、Access、MySql");
                        }
                    }
                }
                return m_supplier;
            }

        }
    

        /// <summary>
        /// DbFactory实例
        /// </summary>
        public static DbProviderFactory Factory
        {
            get
            {
                if (m_factory == null)
                    m_factory = Supplier.DbFactory();
                return m_factory;
            }
        }

        /// <summary>
        /// 刷新数据库供应者
        /// </summary>
        public static void ResetDbSupplier()
        {
            BaseConfigs.ResetConfig();
            DbSupplier.ResetDBSupplier();
            m_connStr = null;
            m_factory = null;
            m_supplier = null;
        }

        #endregion   

        #region ExecuteNonQuery

        #region ExecuteNonQuery不返回刚插入记录的自增ID值

        /// <summary>
        /// 执行SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        /// eg: int id = ExecuteNonQuery("SELECT * FROM [TABLE]");
        /// </remarks>
        /// <param name="comTxt">SQL语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery( string comTxt)
        {
            return ExecuteNonQuery(CommandType.Text, comTxt);
        }

        /// <summary>
        /// 执行SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(CommandType.StoredProcedure, "MySpName")
        /// int id = ExecuteNonQuery(CommandType.Text,"SELECT * FROM [TABLE]")
        /// </remarks>
        /// <param name="comType">命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(CommandType comType, string comTxt)
        {
            return ExecuteNonQuery(comType, comTxt, null);
        }

        /// <summary>
        /// 执行SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(CommandType.StoredProcedure, "MySpName", new SqlParameter("@name", "张三"));
        /// </remarks>
        /// <param name="comType">命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <param name="comParams">DbParameter参数数组</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                return ExecuteNonQuery(conn, comType, comTxt, comParams);
            }
        }

        /// <summary>
        /// 执行SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        ///eg:int id = ExecuteNonQuery(conn, CommandType.StoredProcedure, "MySpName", new SqlParameter("@name", "张三"));
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="comTxt">T存储过程名称或SQL语句</param>
        /// <param name="comPams">SqlParamter参数数组</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(DbConnection conn, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (conn == null) throw new ArgumentNullException("conn");            
            // 创建DbCommand命令,并进行预处理
            using (DbCommand cmd = Factory.CreateCommand())
            {
                bool closeConn = PreCommand(cmd, conn, null, comType, comTxt, comParams);
                int reRows = cmd.ExecuteNonQuery();               
                cmd.Parameters.Clear(); // 清除参数,以便再次使用.
                if (closeConn) conn.Close(); 
                return reRows;
            }
           
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值
        /// 示例:  
        ///  int id = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="paramsValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DbConnection conn, string spName)
        {
            if (conn == null) throw new ArgumentNullException("conn");
            if (spName == null || spName.Trim().Length == 0) throw new ArgumentNullException("spName");

            return ExecuteNonQuery(conn, spName, null);   
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值
        /// 示例: int id = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="paramsValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DbConnection conn, string spName, params object[] paramsValues)
        {
            if (conn == null) throw new ArgumentNullException("conn");
            if (spName == null || spName.Trim().Length == 0) throw new ArgumentNullException("spName");

            // 如果有参数值
            if ((paramsValues != null) && (paramsValues.Length > 0))
            {
                // 从缓存中加载存储过程参数
                DbParameter[] comParams = GetSpParamSet(conn, spName);
                SetParamsValues(comParams, paramsValues);  // 给存储过程分配参数值
                return ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteNonQuery(conn, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行带事务SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        /// eg:  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "MySpName");
        /// </remarks>
        /// <param name="trans">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="comText">存储过程名称或SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DbTransaction trans, string comTxt)
        {
            return ExecuteNonQuery(trans, CommandType.Text, comTxt);
        }

        /// <summary>
        /// 执行带事务SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        /// eg:  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "MySpName");
        /// </remarks>
        /// <param name="trans">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="comText">存储过程名称或SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DbTransaction trans, CommandType comType, string comTxt)
        {
            return ExecuteNonQuery(trans, comType, comTxt, null);
        }

        /// <summary>
        /// 执行带事务SQL命令，返回受影响行数，不返回任何结果集。
        /// </summary>
        /// <remarks>
        /// eg:  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "MySpName", new SqlParameter("@name", "lilei"));
        /// </remarks>
        /// <param name="trans">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="comText">存储过程名称或SQL语句</param>
        /// <param name="comParams">SqlParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DbTransaction trans, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans.Connection == null)throw new ArgumentException("The transaction connection is null", "trans");

            using (DbCommand cmd = Factory.CreateCommand())
            {
                bool closeConn = PreCommand(cmd, trans.Connection, trans, comType, comTxt, comParams);
                int reRows = cmd.ExecuteNonQuery(); // 执行DbCommand命令,并返回结果.
                cmd.Parameters.Clear();// 清除参数集,以便再次使用.
                return reRows;
            }
        }
        
        #endregion

        #region ExecuteNonQuery返回刚插入记录的自增ID值

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <param name="comText">SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, string comTxt)
        {
            return ExecuteNonQuery(out lastId, CommandType.Text, comTxt, null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(conn, CommandType.StoredProcedure, "MySpName");
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">T存储过程名称或SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId,CommandType comType, string comTxt)
        {
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                return ExecuteNonQuery(out lastId, conn, comType, comTxt, null);
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <param name="comText">SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, DbConnection conn, string comTxt)
        {
            return ExecuteNonQuery(out lastId, conn, CommandType.Text, comTxt, null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(out lastId, CommandType.StoredProcedure, "MySpName");
        /// </remarks>
        /// <param name="comType">命令类型</param>
        /// <param name="comText">T存储过程名称或SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, DbConnection conn, CommandType comType, string comTxt)
        {
            return ExecuteNonQuery(out lastId, conn, comType, comTxt, null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(out lastId, CommandType.StoredProcedure, "MySpName", new DbParameter("@name", "zhangsan"));
        /// </remarks>
        /// <param name="comType">命令类型</param>
        /// <param name="comText">T存储过程名称或SQL语句</param>
        /// <param name="comParams">DbParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                return ExecuteNonQuery(out lastId, conn, comType, comTxt, comParams);
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(out lastId, CommandType.StoredProcedure, "MySpName", new DbParameter("@name", "zhangsan"));
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型</param>
        /// <param name="comText">T存储过程名称或SQL语句</param>
        /// <param name="comParams">DbParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, DbConnection conn, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (conn == null) throw new ArgumentNullException("conn");
            if (Supplier.GetLastId().Trim() == "") throw new ArgumentNullException("GetLastId is \"\"");

            // 创建DbCommand命令,并进行预处理
            using (DbCommand cmd = Factory.CreateCommand())
            {
                bool closeConn = PreCommand(cmd, conn, null, comType, comTxt, comParams);

                int reRows = cmd.ExecuteNonQuery();// 执行命令      

                cmd.Parameters.Clear(); // 清除参数,以便再次使用.
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = Supplier.GetLastId();

                lastId = Convert.ToInt64(cmd.ExecuteScalar());

                if (closeConn) conn.Close();

                return reRows;
            }
        }
        
        /// <summary>
        /// 执行带事务的DbCommand(指定参数).
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(out lastId, trans, CommandType.StoredProcedure, "MySpName");
        /// </remarks>
        /// <param name="trans">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型</param>
        /// <param name="comText">存储过程名称或SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, DbTransaction trans, CommandType comType, string comTxt)
        {
            return ExecuteNonQuery(out lastId, trans, comType, comTxt, null);
        }
       
        /// <summary>
        /// 执行带事务的DbCommand(指定参数).
        /// </summary>
        /// <remarks>
        /// eg:int id = ExecuteNonQuery(out lastId, trans, CommandType.StoredProcedure, "GetOrders", new DbParameter("@name", "zhangsan"));
        /// </remarks>
        /// <param name="trans">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型</param>
        /// <param name="comText">存储过程名称或SQL语句</param>
        /// <param name="comParams">DbParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(out long lastId, DbTransaction trans, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans.Connection == null) throw new ArgumentException("The transaction connection is null", "trans");

            // 预处理
            DbCommand cmd = Factory.CreateCommand();
            bool closeConn =PreCommand(cmd, trans.Connection, trans, comType, comTxt, comParams);

            // 执行
            int reRows = cmd.ExecuteNonQuery();
            // 清除参数,以便再次使用.
            cmd.Parameters.Clear();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = Supplier.GetLastId();
            lastId = Convert.ToInt64(cmd.ExecuteScalar().ToString());
            return reRows;
        }
        
        #endregion

        #endregion

        #region ExecuteReader

        /// <summary>
        /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  DbDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="spName">T存储过程名</param>
        /// <param name="paramValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection conn, string spName, params object[] paramValues)
        {
            if (conn == null) throw new ArgumentNullException("conn");
            if (spName == null || spName.Trim().Length == 0) throw new ArgumentNullException("spName");

            if ((paramValues != null) && (paramValues.Length > 0))
            {
                DbParameter[] comParams = GetSpParamSet(conn, spName);

                SetParamsValues(comParams, paramValues);

                return ExecuteReader(conn, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteReader(conn, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="comTxt">SQL语句</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(string comTxt)
        {
            return ExecuteReader(CommandType.Text, comTxt);
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(CommandType comType, string comTxt)
        {
            return ExecuteReader(comType, comTxt, (DbParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <param name="comParams">DbParameters参数数组,如果没有参数则为'null'</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (ConnStr == null || ConnStr.Length == 0) throw new ArgumentNullException("ConnStr");
            using (DbConnection conn = Factory.CreateConnection())
            {
                try
                {
                    conn.ConnectionString = ConnStr;
                    conn.Open();
                    return ExecuteReader(conn, (DbTransaction)null, comType, comTxt, comParams);
                }
                catch
                {
                    if (conn != null) conn.Close();
                    throw;
                }
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection conn, CommandType comType, string comTxt)
        {
            return ExecuteReader(conn, comType, comTxt, (DbParameter)null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <param name="comParams">DbParameters参数数组,如果没有参数则为'null'</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection conn, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            return ExecuteReader(conn, null, comType, comTxt, comParams);
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="trans">一个有效的事务,或者为 'null'</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection conn, DbTransaction trans, CommandType comType, string comTxt)
        {
            return ExecuteReader(conn, trans, comType, comTxt, null);
        }
        
        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="trans">一个有效的事务,或者为 'null'</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <param name="comParams">DbParameters参数数组,如果没有参数则为'null'</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection conn, DbTransaction trans, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (conn == null) throw new ArgumentNullException("conn");

            using (DbCommand cmd = Factory.CreateCommand())
            {
                bool closeConn = false;
                try
                {
                    closeConn = PreCommand(cmd, conn, trans, comType, comTxt, comParams);
                    DbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    foreach (DbParameter comParam in cmd.Parameters)
                    {
                        if (comParam.Direction == ParameterDirection.Input)
                        {
                            // 清除参数,以便再次使用..
                            cmd.Parameters.Clear();
                            break;
                        }
                    }
                    return dataReader;
                }
                catch
                {
                    if (closeConn) conn.Close();
                    throw;
                }
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="trans">一个有效的事务,或者为 'null'</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <param name="comParams">DbParameters参数数组,如果没有参数则为'null'</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbTransaction trans, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans.Connection == null) throw new ArgumentException("The transaction connection is null.", "trans");

            return ExecuteReader(trans.Connection, trans, comType, comTxt, comParams);

        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="trans">一个有效的事务,或者为 'null'</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名或SQL语句</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbTransaction trans, CommandType comType, string comTxt)
        {
            return ExecuteReader(trans.Connection, trans, comType, comTxt, (DbParameter[])null);
        }        
       
        /// <summary>
        /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 
        /// 示例:  
        ///  DbDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="trans">一个有效的连接事务</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="paramValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbTransaction trans, string spName, params object[] paramValues)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans.Connection == null) throw new ArgumentException("The transaction transaction is null.", "trans");
            if (spName == null || spName.Trim().Length == 0) throw new ArgumentNullException("spName");

            if ((paramValues != null) && (paramValues.Length > 0))
            {
                DbParameter[] comParams = GetSpParamSet(trans.Connection, spName);
                SetParamsValues(comParams, paramValues);
                return ExecuteReader(trans, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteReader(trans, CommandType.StoredProcedure, spName);
            }
        }

        #endregion

        #region ExecuteDataSet方法


        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataSet("SELECT * FROM [table1]");
        /// </remarks>
        /// <param name="commandText">存储过程名称或SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(string commandText)
        {
            return ExecuteDataSet(CommandType.Text, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataSet(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="ConnectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            return ExecuteDataSet(commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataSet(connString, CommandType.StoredProcedure, "GetOrders", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="ConnStr">一个有效的数据库连接字符串</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comText">存储过程名称或SQL语句</param>
        /// <param name="comParams">SqlParamters参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(CommandType comType, string comText, params DbParameter[] comParams)
        {
            if (ConnStr == null || ConnStr.Length == 0) throw new ArgumentNullException("ConnStr");

            // 创建并打开数据库连接对象,操作完成释放对象.

            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();

                return ExecuteDataSet(conn, comType, comText, comParams);
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataSet(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comText">存储过程名或SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(DbConnection conn, CommandType comType, string comText)
        {
            return ExecuteDataSet(conn, comType, comText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataSet(conn, CommandType.StoredProcedure, "GetOrders", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comdType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comText">存储过程名或SQL语句</param>
        /// <param name="comParams">SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(DbConnection conn, CommandType comType, string comText, params DbParameter[] comParams)
        {
            if (conn == null) throw new ArgumentNullException("conn");

            // 预处理
            DbCommand cmd = Factory.CreateCommand();
            bool closeConn =PreCommand(cmd, conn, (DbTransaction)null, comType, comText, comParams);

            // 创建DbDataAdapter和DataSet.
            using (DbDataAdapter da = Factory.CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();

                da.Fill(ds);
                cmd.Parameters.Clear();

                if (closeConn)
                    conn.Close();

                return ds;
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输入参数和返回值.
        /// 示例.:  
        ///  DataSet ds = ExecuteDataSet(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="paramValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(DbConnection conn, string spName, params object[] paramValues)
        {
            if (conn == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((paramValues != null) && (paramValues.Length > 0))
            {
                // 比缓存中加载存储过程参数
                DbParameter[] comParams = GetSpParamSet(conn, spName);
                SetParamsValues(comParams, paramValues);// 给存储过程参数分配值

                return ExecuteDataSet(conn, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteDataSet(conn, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定事务的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataSet(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="trans">事务</param>
        /// <param name="comdType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comText">存储过程名或SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(DbTransaction trans, CommandType comType, string comText)
        {
            return ExecuteDataSet(trans, comType, comText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行指定事务的命令,指定参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataSet(trans, CommandType.StoredProcedure, "GetOrders", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">事务</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comText">存储过程名或SQL语句</param>
        /// <param name="comParams">SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(DbTransaction trans, CommandType comType, string comText, params DbParameter[] comParams)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans != null && trans.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // 预处理
            DbCommand cmd = Factory.CreateCommand();
            bool closeConn =PreCommand(cmd, trans.Connection, trans, comType, comText, comParams);

            // 创建 DataAdapter & DataSet
            using (DbDataAdapter da = Factory.CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
        }

        /// <summary>
        /// 执行指定事务的命令,指定参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输入参数和返回值.
        /// 示例.:  
        ///  DataSet ds = ExecuteDataSet(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="trans">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="paramValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataSet(DbTransaction trans, string spName, params object[] paramValues)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans != null && trans.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((paramValues != null) && (paramValues.Length > 0))
            {
                // 从缓存中加载存储过程参数
                DbParameter[] comParams =GetSpParamSet(trans.Connection, spName);

                // 给存储过程参数分配值
                SetParamsValues (comParams, paramValues);

                return ExecuteDataSet(trans, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteDataSet(trans, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteDataSet数据集命令结束

        #region ExecuteScalar
        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// eg: object orderCount = ExecuteScalar( "select * from[table]");
        /// </remarks>
        /// <param name="comTxt">SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string comTxt)
        {
            return ExecuteScalar(CommandType.Text,comTxt);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// eg: object orderCount = ExecuteScalar( CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(CommandType comType, string comTxt)
        {
            return ExecuteScalar(comType,comTxt,(DbParameter[])null);
        }
        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// eg: object orderCount = ExecuteScalar( CommandType.StoredProcedure, "GetOrderCount", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <param name="comParams">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar( CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (ConnStr == null || ConnStr.Trim().Length<=0) throw new ArgumentNullException("ConnStr");

            // 创建并打开数据库连接对象,操作完成释放对象.
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnStr;
                conn.Open();
                return ExecuteScalar(conn, comType, comTxt, comParams);
            }
        }
        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// eg: object orderCount = ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(DbConnection conn, CommandType comType, string comTxt)
        {
            return ExecuteScalar(conn,comType,comTxt,(DbParameter[])null);
        }
        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// eg: object orderCount = ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">一个有效的数据库连接对象</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <param name="comParams">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(DbConnection conn, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (conn == null) throw new ArgumentNullException("connection");

            // 创建DbCommand命令,并进行预处理
            using (DbCommand cmd = Factory.CreateCommand())
            {
                bool closeConn = PreCommand(cmd, conn, (DbTransaction)null, comType, comTxt, comParams);                
                object retval = cmd.ExecuteScalar();// 执行DbCommand命令,并返回结果.                
                cmd.Parameters.Clear();// 清除参数,以便再次使用.
                if (closeConn) conn.Close();
                return retval;
            }
        }

        /// <summary>
        /// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例: object orderCount = ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="trans">一个有效的连接事务</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(DbTransaction trans, CommandType comType, string comTxt)
        {
            return ExecuteScalar(trans,comType,comTxt,(DbParameter)null);
        }
        /// <summary>
        /// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例: object orderCount = ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">一个有效的连接事务</param>
        /// <param name="comType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="comTxt">存储过程名称或SQL语句</param>
        /// <param name="comParams">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(DbTransaction trans, CommandType comType, string comTxt, params DbParameter[] comParams)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if ( trans.Connection == null) throw new ArgumentException("The transaction connection is null", "trans");

            // 创建DbCommand命令,并进行预处理
            using (DbCommand cmd = Factory.CreateCommand())
            {
                bool closeConn = PreCommand(cmd, trans.Connection, trans, comType, comTxt, comParams);

                object retval = cmd.ExecuteScalar();
                cmd.Parameters.Clear(); // 清除参数,以便再次使用.
                return retval;
            }
        }

        /// <summary>
        /// 执行指定数据库事务的命令,指定参数值,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例: object orderCount = ExecuteScalar(trans, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="trans">一个有效的连接事务</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="paramValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(DbTransaction trans, string spName, params object[] paramValues)
        {
            if (trans == null) throw new ArgumentNullException("trans");
            if (trans.Connection == null) throw new ArgumentException("The transaction connection is null", "trans");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果有参数值
            if ((paramValues != null) && (paramValues.Length > 0))
            {
                DbParameter[] comParams = GetSpParamSet(trans.Connection, spName);
                // 给存储过程参数赋值
                SetParamsValues(comParams, paramValues);
                return ExecuteScalar(trans, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteScalar(trans, CommandType.StoredProcedure, spName);
            }
        }
        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数值,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例: objcect orderCount = ExecuteScalar(conn, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(DbConnection conn, string spName, params object[] paramValues)
        {
            if (conn== null) throw new ArgumentNullException("conn");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((paramValues != null) && (paramValues.Length > 0))
            {
                // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
                DbParameter[] comParams = GetSpParamSet(conn, spName);

                // 给存储过程参数赋值
                SetParamsValues(comParams, paramValues);
                return ExecuteScalar(conn, CommandType.StoredProcedure, spName, comParams);
            }
            else
            {
                return ExecuteScalar(conn, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region UpdateDataset 更新数据集
        /// <summary>
        /// 执行数据集更新到数据库,指定inserted, updated, deleted命令.
        /// </summary>
        /// <remarks>
        /// eg: UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        /// </remarks>
        /// <param name="insertCommand">[追加记录]一个有效的SQL语句或存储过程</param>
        /// <param name="deleteCommand">[删除记录]一个有效的SQL语句或存储过程</param>
        /// <param name="updateCommand">[更新记录]一个有效的SQL语句或存储过程</param>
        /// <param name="dataSet">要更新到数据库的DataSet</param>
        /// <param name="tableName">要更新到数据库的DataTable</param>
        public static void UpdateDataset(DbCommand insertCommand, DbCommand deleteCommand, DbCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");
            if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // 创建DbDataAdapter,当操作完成后释放.
            using (DbDataAdapter dataAdapter = Factory.CreateDataAdapter())
            {
                // 设置数据适配器命令
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // 更新数据集改变到数据库
                dataAdapter.Update(dataSet, tableName);

                // 提交所有改变到数据集.
                dataSet.AcceptChanges();
            }
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// OBJECT数组克隆（深层拷贝）.
        /// </summary>
        /// <param name="src">原始数组</param>
        /// <returns>返回克隆后的数组</returns>
        private static Object[] Clone(object[] src)
        {
            object[] objs = new object[src.Length];

            for (int i = 0, j = src.Length; i < j; i++)
                objs[i] = (object)((ICloneable)src[i]).Clone();
            return objs;
        }

        /// <summary>
        /// 将DbParameter参数数组(参数值)分配给DbCommand命令.
        /// 这个方法将给任何一个参数分配DBNull.Value;
        /// 该操作将阻止默认值的使用.
        /// </summary>
        /// <param name="com">命令</param>
        /// <param name="comParams">DbParameters数组</param>
        private static void SetNullParams(DbCommand com, DbParameter[] comParams)
        {
            if (com == null) throw new ArgumentNullException("请确定command对象是否存在。");
            if (comParams != null)
            {
                foreach (DbParameter cp in comParams)
                {
                    if (cp != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((cp.Direction == ParameterDirection.InputOutput || cp.Direction == ParameterDirection.Input) && (cp.Value == null))
                            cp.Value = DBNull.Value;

                        com.Parameters.Add(cp);
                    }
                }
            }
        }
        /// <summary>
        /// 将一个对象数组分配给DbParameter参数数组.
        /// </summary>
        /// <param name="comPams">要分配值的DbParameter参数数组</param>
        /// <param name="paramsValues">将要分配给存储过程参数的对象数组</param>
        private static void SetParamsValues(DbParameter[] comParams, object[] paramsValues)
        {
            if ((comParams == null) || (paramsValues == null)) return;
            if (comParams.Length != paramsValues.Length)
                throw new ArgumentException("参数值个数与参数个数不匹配.");

            // 给参数赋值
            for (int t = 0, j = comParams.Length; t < j; t++)
            {
                // 判断当前的数组值是否从IDbDataParameter派生
                if (paramsValues[t] is IDbDataParameter)
                {
                    IDbDataParameter param = (IDbDataParameter)paramsValues[t];
                    if (param.Value == null) comParams[t].Value = DBNull.Value;
                    else comParams[t].Value = param.Value;
                }
                else if (paramsValues[t] == null)
                {
                    comParams[t].Value = DBNull.Value;
                }
                else
                {
                    comParams[t].Value = paramsValues[t];
                }
            }
        }

        /// <summary>
        /// 探索运行时的存储过程,返回DbParameter参数数组.
        /// 初始化参数值为 DBNull.Value.
        /// </summary>
        /// <param name="conn">一个有效的数据库连接</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="returnValueParam">是否包含返回值参数</param>
        /// <returns>返回DbParameter参数数组</returns>
        private static DbParameter[] DiscoverSetSpParam(DbConnection conn, string spName, bool returnValueParam)
        {
            if (conn == null || spName == null || spName.Length == 0)
                throw new ArgumentNullException("conn or spName");

            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = spName;
                conn.Open();
                // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
                Supplier.DeriveParameters(cmd);
                conn.Close();

                if (!returnValueParam) cmd.Parameters.RemoveAt(0);

                DbParameter[] t_params = new DbParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(t_params, 0);

                foreach (DbParameter param in t_params)
                    param.Value = DBNull.Value;

                return t_params;
            }
        }
        /// <summary>
        /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
        /// 如果连接是打开的,则为true,其它情况下为false.
        /// </summary>
        /// <param name="com">要处理的DbCommand</param>
        /// <param name="conn">数据库连接</param>
        /// <param name="trans">一个有效的事务或者是null值</param>
        /// <param name="comType">命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param name="comTxt">存储过程名或都SQL命令文本</param>
        /// <param name="comParams">和命令相关联的DbParameter参数数组,如果没有参数为'null'</param>
        private static bool PreCommand(DbCommand com, DbConnection conn, DbTransaction trans, CommandType comType, string comTxt, DbParameter[] comParams)
        {
            if (com == null) throw new ArgumentNullException("command不能为空");
            if (comTxt == null || comTxt.Trim().Length == 0) throw new ArgumentNullException("commandText不能为空");

            //如果数据库连接状态为关闭则打开它
            bool connState = false;
            if (conn.State != ConnectionState.Open)
            {
                connState = true;
                conn.Open();
            }

            com.Connection = conn;// 给命令分配一个数据库连接.
            com.CommandText = comTxt;
            com.CommandType = comType;  // 设置命令类型.

            // 分配事务
            if (trans != null)
            {
                if (trans.Connection == null)
                    throw new ArgumentException("The transaction connection is null", "transaction");
                com.Transaction = trans;
            }

            // 分配命令参数
            if (comParams != null)
                SetNullParams(com, comParams);

            return connState;
        }
        

        #region 检索存储过程
        /// <summary>
        /// [私有]返回指定的存储过程的参数集(使用连接对象)
        /// </summary>
        /// <param name="conne">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <returns>返回DbParameter参数数组</returns>
        private static DbParameter[] GetSpParamSet(DbConnection conn, string spName)
        {
            return GetSpParamSet(conn,spName,false);
        }
        /// <summary>
        /// [私有]返回指定的存储过程的参数集(使用连接对象)
        /// </summary>
        /// <param name="conne">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="returnValueParam">是否包含返回值参数</param>
        /// <returns>返回DbParameter参数数组</returns>
        private static DbParameter[] GetSpParamSet(DbConnection conn, string spName, bool returnValueParam)
        {
            if (conn == null || spName == null || spName.Length == 0) throw new ArgumentNullException("conn or spName");

            using (DbConnection clonedConnection = (DbConnection)((ICloneable)conn).Clone())
            {
                string hashKey = clonedConnection.ConnectionString + ":" + spName + (returnValueParam ? ":returnValueParameter" : "");
                if (m_paramCache[hashKey] == null)
                    m_paramCache[hashKey] = DiscoverSetSpParam(clonedConnection, spName, returnValueParam);

                return (DbParameter[])Clone((object[])m_paramCache[hashKey]);
            }
           
        }
        #endregion

        #endregion

        #region 缓存方法

        /// <summary>
        /// 将参数数组添加到缓存.
        /// </summary>
        /// <param name="comText">存储过程名或SQL语句</param>
        /// <param name="comParams">要缓存的参数对象</param>
        public static void GetParamFromCache(string comTxt, params DbParameter[] comParams)
        {
            if (ConnStr == null || ConnStr.Length == 0) throw new ArgumentNullException("ConnStr");
            if (comTxt == null || comTxt.Length == 0) throw new ArgumentNullException("comTxt");
            m_paramCache[ ConnStr + ":" + comTxt] = comParams;
        }

        /// <summary>
        /// 从缓存中获取参数数组.
        /// </summary>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <returns>参数数组</returns>
        public static DbParameter[] GetParamFromCache(string comTxt)
        {
            if (ConnStr == null || ConnStr.Length == 0) throw new ArgumentNullException("ConnStr");
            if (comTxt == null || comTxt.Length == 0) throw new ArgumentNullException("comTxt");
            DbParameter[] cachedParams =( DbParameter[])m_paramCache[ConnStr + ":" + comTxt];
            if (cachedParams == null) return null;
            else return (DbParameter[])Clone(cachedParams);
        }

        #endregion 缓存方法结束

        #region 生成参数

        /// <summary>
        /// 生成输入参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Value">参数值</param>
        /// <returns>返回生成的参数</returns>
        public static DbParameter CreateInParam(string ParamName, DbType DbType, object Value)
        {
            return CreateParam(ParamName, DbType, ParameterDirection.Input, Value);
        }
        /// <summary>
        /// 生成输入参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>返回生成的参数</returns>
        public static DbParameter CreateInParam(string ParamName, DbType DbType, int Size, object Value)
        {
            return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <returns>返回生成的参数</returns>
        public static DbParameter CreateOutParam(string paramName, DbType DbType)
        {
            return CreateParam(paramName, DbType, ParameterDirection.Output, null);
        }
        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>返回生成的参数</returns>
        public static DbParameter CreateOutParam(string paramName, DbType DbType, int Size)
        {
            return CreateParam(paramName, DbType, Size, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Direction">参数输入、输出类型</param>
        /// <param name="Value">参数值</param>
        /// <returns>返回生成的参数</returns>
        public static DbParameter CreateParam(string paramName, DbType DbType, ParameterDirection Direction, object Value)
        {
            DbParameter param = Supplier.CreateParam(paramName, DbType);
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;
            return param;
        }
        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数输入、输出类型</param>
        /// <param name="Value">参数值</param>
        /// <returns>返回生成的参数</returns>
        public static DbParameter CreateParam(string paramName, DbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            DbParameter param = Supplier.CreateParam(paramName, DbType, Size);
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;
            return param;
        }

        #endregion 生成参数结束
    }
}
